﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Query2Object;

namespace Access
{
    class OledbStruts:IStruts
    {
        private List<ColumnProperty> Meta;

        private string ConnStr;

        public OledbStruts(string ConnectionString)
        {
            ConnStr = ConnectionString;
        }

        #region IStruts 成员

        public string[] SpecialUsing
        {
            get
            {
                return new string[] { "using System.Data.OleDb;" };
            }
        }

        public string PrintCode(string Id)
        {
            try
            {
                List<string> Tables = new List<string>();
                using (OleDbConnection conn = new OleDbConnection(ConnStr))
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }

                    DataTable sc = conn.GetSchema("Tables");
                    conn.Close();
                    foreach (DataRow row in sc.Rows)
                    {
                        if (row[3].ToString().Equals("TABLE"))
                        {
                            Tables.Add(row[2].ToString());
                        }
                    }
                }
                StringBuilder codes = new StringBuilder();
                //AddUsingInfo(codes);
                foreach (String name in Tables)
                {
                    codes.Append(PrintTable(name));
                }
                return codes.ToString();
            }
            catch (OleDbException ex)
            {
                throw new CodeGenerateException("Sql Error!", ex);
            }
            catch (Exception ex)
            {
                throw new CodeGenerateException("UnKown Error!", ex);
            }
        }

        private string PrintTable(string TableName)
        {
            Meta = GetDataColumns(TableName);
            string KeyName = "";
            foreach (ColumnProperty cp in Meta)
            {
                if (cp.IsKey)
                {
                    KeyName = cp.ColumnName;
                    break;
                }
            }
            StringBuilder CodeText = new StringBuilder(1000);
            //CodeText.Append("namespace WebTest\r\n{\r\n");
            CodeText.Append("public class " + TableName.Replace(" ", "")).Append(":Query2Object.ActiveBase,Query2Object.IQueryable\r\n");
            CodeText.Append("{\r\n");
            CodeText.Append("\tpublic string GetPrimaryKeyName()\r\n");
            CodeText.Append("\t{\r\n");
            CodeText.Append("\t\t").Append("return \"").Append(KeyName).Append("\";\r\n");
            CodeText.Append("\t}\r\n");
            CodeText.Append("\tpublic string GetObjectTableName()\r\n");
            CodeText.Append("\t{\r\n");
            CodeText.Append("\t\t").Append("return \"").Append(TableName).Append("\";\r\n");
            CodeText.Append("\t}\r\n");
            AddPropertys(CodeText);
            AddExpPropertys(CodeText);
            AddGetPrepareParameter(CodeText);
            AddLoadReader(CodeText);
            AddCreator(CodeText, TableName);
            AddInsertColumn(CodeText, TableName);
            CodeText.Append("\r\n}");
            return CodeText.ToString();
        }

        private void AddCreator(StringBuilder txt, string TableName)
        {
            txt.Append("\r\n");
            txt.Append("\tpublic Object CreateNewInstance()\r\n");
            txt.Append("\t{\r\n");
            txt.Append("\t\treturn new ").Append(TableName.Replace(" ","")).Append("();\r\n");
            txt.Append("\t}\r\n");
        }

        private void AddPropertys(StringBuilder txt)
        {
            txt.Append("\r\n");
            foreach (ColumnProperty cp in Meta)
            {
                txt.Append("\t").Append("private ").Append(cp.DataType).Append(" ").Append("_").Append(cp.ColumnName).Append(";\r\n");
                txt.Append("\t").Append("public ").Append(cp.DataType).Append(" ").Append(cp.ColumnName).Append("\r\n");
                txt.Append("\t{\r\n");
                txt.Append("\t\tget{ return _").Append(cp.ColumnName).Append("; }\r\n");
                txt.Append("\t\tset{ _").Append(cp.ColumnName).Append(" = value; }\r\n");
                txt.Append("\t}\r\n");
            }
            txt.Append("\r\n");
        }

        private void AddExpPropertys(StringBuilder txt)
        {
            txt.Append("\r\n");
            foreach (ColumnProperty cp in Meta)
            {
                txt.Append("\t").Append("public static Exp _").Append(cp.ColumnName.ToUpper()).Append("_ = new Exp(\"").Append(cp.ColumnName).Append("\");\r\n");
            }
            txt.Append("\r\n");
        }

        private void AddGetPrepareParameter(StringBuilder txt)
        {
            OleDbType tp;
            txt.Append("\r\n");
            txt.Append("\t").Append("public List<IDbDataParameter> PreparePrameter()\r\n");
            txt.Append("\t{\r\n");
            txt.Append("\t\tIDbDataParameter p = null;\r\n");
            txt.Append("\t\tList<IDbDataParameter> rs = new List<IDbDataParameter>();\r\n");
            foreach (ColumnProperty cp in Meta)
            {
                tp = (OleDbType)System.Enum.Parse(typeof(OleDbType), cp.TypeNumber.ToString());
                if ((cp.DataType != "System.Boolean") && (cp.DataType != "System.Int32") && (cp.DataType != "System.Int16") && (cp.DataType != "System.Decimal") && (cp.DataType != "System.Single") && (cp.DataType != "System.Double") && (cp.DataType != "System.Int64"))
                {
                    txt.Append("\t\tif (_" + cp.ColumnName + " != null)\r\n");
                    txt.Append("\t\t{\r\n");
                    if (cp.DataType != "System.DateTime")
                    {
                        txt.Append("\t\t\t").Append("p = new OleDbParameter(\"@" + cp.ColumnName + "\",OleDbType." + tp.ToString() + ");\r\n");
                        txt.Append("\t\t\t").Append("p.Value = _").Append(cp.ColumnName).Append(";\r\n");
                        txt.Append("\t\t\t").Append("rs.Add(p);\r\n");
                    }
                    else
                    {
                        txt.Append("\t\t\t").Append("if (_" + cp.ColumnName + ".Ticks > DateTime.MinValue.AddYears(1800).Ticks)\r\n");
                        txt.Append("\t\t\t{\r\n");
                        txt.Append("\t\t\t\t").Append("p = new OleDbParameter(\"@" + cp.ColumnName + "\",OleDbType." + tp.ToString() + ");\r\n");
                        txt.Append("\t\t\t\t").Append("p.Value = _").Append(cp.ColumnName).Append(";\r\n");
                        txt.Append("\t\t\t\t").Append("rs.Add(p);\r\n");
                        txt.Append("\t\t\t}\r\n");
                    }
                    txt.Append("\t\t}\r\n");
                }
                else
                {
                    txt.Append("\t\t").Append("p = new OleDbParameter(\"@" + cp.ColumnName + "\",OleDbType." + tp.ToString() + ");\r\n");
                    txt.Append("\t\t").Append("p.Value = _").Append(cp.ColumnName).Append(";\r\n");
                    txt.Append("\t\t").Append("rs.Add(p);\r\n");
                }
            }
            txt.Append("\t\treturn rs;\r\n");
            txt.Append("\t}\r\n");
            txt.Append("\r\n");
        }

        private void AddLoadReader(StringBuilder txt)
        {
            txt.Append("\r\n");
            txt.Append("\t").Append("public void LoadFromReader(IDataReader Rd)\r\n");
            txt.Append("\t{\r\n");
            foreach (ColumnProperty cp in Meta)
            {
                txt.Append("\t\tif(!Rd.IsDBNull(Rd.GetOrdinal(\"" + cp.ColumnName + "\")))\r\n");
                txt.Append("\t\t{\r\n");
                txt.Append("\t\t\t_").Append(cp.ColumnName).Append(" = (").Append(cp.DataType).Append(")Rd[\"").Append(cp.ColumnName).Append("\"];\r\n");
                txt.Append("\t\t}\r\n");
            }
            txt.Append("\t}\r\n");
            txt.Append("\r\n");
        }

        private void AddInsertColumn(StringBuilder txt, string TableName)
        {
            List<string> cols = new List<string>();
            foreach (ColumnProperty cp in Meta)
            {
                if (!cp.IsAuto)
                {
                    cols.Add(cp.ColumnName);
                }
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO ").Append(TableName).Append("(");
            for (int i = 0; i < cols.Count; i++)
            {
                if (i < (cols.Count - 1))
                {
                    sb.Append("[").Append(cols[i]).Append("],");
                }
                else
                {
                    sb.Append("[").Append(cols[i]).Append("]");
                }
            }
            sb.Append(") VALUES (");
            for (int i = 0; i < cols.Count; i++)
            {
                if (i < (cols.Count - 1))
                {
                    sb.Append("@").Append(cols[i]).Append(" ,");
                }
                else
                {
                    sb.Append("@").Append(cols[i]).Append(" ");
                }
            }
            sb.Append(")");
            txt.Append("\r\n");
            txt.Append("\t").Append("public string GetInsertSql()\r\n");
            txt.Append("\t{\r\n");
            txt.Append("\t\treturn \"").Append(sb.ToString()).Append("\";");
            txt.Append("\t}\r\n");
            txt.Append("\r\n");
        }

        private List<ColumnProperty> GetDataColumns(string TableName)
        {
            ColumnProperty ppt = null;
            DataTable Schema = null;
            List<ColumnProperty> Rs = new List<ColumnProperty>(20);
            using (OleDbConnection conn = new OleDbConnection(ConnStr))
            {
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + TableName + "] WHERE 1 = 0", conn);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                using (OleDbDataReader rd = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    Schema = rd.GetSchemaTable();
                    rd.Close();
                }
            }
            foreach (DataRow row in Schema.Rows)
            {
                ppt = new ColumnProperty();
                ppt.ColumnName = row[0].ToString();
                ppt.TypeNumber = int.Parse(row[6].ToString());
                ppt.DataType = row[5].ToString();
                ppt.IsKey = bool.Parse(row[12].ToString().ToLower());
                ppt.IsAuto = bool.Parse(row[13].ToString().ToLower());
                Rs.Add(ppt);
            }
            return Rs;
        }
        private void AddUsingInfo(StringBuilder txt)
        {
            txt.Append("using System;\r\n");
            txt.Append("using System.Data;\r\n");
            txt.Append("using System.Data.OleDb;\r\n");
            txt.Append("using System.Collections.Generic;\r\n");
            txt.Append("using Query2Object;\r\n");
        }

        #endregion
    }
}
